package com.fann;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Driver;

public class JoinDAO {

	public static void main(String[] args) {

	}

	public boolean add(Join JO) {
		
		boolean addJudge = true;
		SimpleDateFormat nowdate = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = nowdate.format(new java.util.Date());

		if (addJudge) {
			Connection conn = null;
			PreparedStatement pstmt = null;
			try {
				DriverManager.registerDriver(new Driver());
				conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic?useUnicode=true&characterEncoding=UTF-8","root", "");

				String strSql = "INSERT INTO jo_class"
						+ "(jo_id, user_id, jo_Sdate, jo_Edate, cr_id, lo_id, ls_id, cs_id, op_Sdate, op_Edate, up_date) "
						+ "values "
						+ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
				pstmt = conn.prepareStatement(strSql);
				
				pstmt.setString(1, JO.jo_id);
				pstmt.setString(2, JO.user_id);
				pstmt.setString(3, JO.jo_Sdate);
				pstmt.setString(4, JO.jo_Edate);
				pstmt.setString(5, JO.cr_id);
				pstmt.setString(6, JO.lo_id);
				pstmt.setString(7, JO.ls_id);
				pstmt.setString(8, JO.cs_id);
				pstmt.setString(9, JO.op_Sdate);
				pstmt.setString(10, JO.op_Edate);
				System.out.print(strSql);
				pstmt.setString(11, time);
//				int rowCount = 
						pstmt.executeUpdate();
//				System.out.println(rowCount);
			} catch (SQLException e) {
				e.printStackTrace();
				addJudge = false;
			} finally {
				try {
					if (conn != null && pstmt != null) {
						pstmt.close();
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return addJudge;
	}

	public int update(Join JO) {

		int rowCount = 0;
		SimpleDateFormat nowdate = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = nowdate.format(new java.util.Date());
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			DriverManager.registerDriver(new Driver());
			conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic?useUnicode=true&characterEncoding=UTF-8","root", "");

			String strSql = "update jo_class set jo_id=?, user_id=?, jo_Sdate=?, jo_Edate=?, cr_id=?, lo_id=?, ls_id=?, cs_id=?, op_Sdate=?, op_Edate=?, up_date=? where id=?";
			pstmt = conn.prepareStatement(strSql);
			
			pstmt.setString(1, JO.jo_id);
			pstmt.setString(2, JO.user_id);
			pstmt.setString(3, JO.jo_Sdate);
			pstmt.setString(4, JO.jo_Edate);
			pstmt.setString(5, JO.cr_id);
			pstmt.setString(6, JO.lo_id);
			pstmt.setString(7, JO.ls_id);
			pstmt.setString(8, JO.cs_id);
			pstmt.setString(9, JO.op_Sdate);
			pstmt.setString(10, JO.op_Edate);
			pstmt.setString(11, time);
			
			pstmt.setInt(12, JO.id);
			rowCount = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rowCount;
	}

	public int delete(String id) {
		int rowCount = 0;
		try {
			DriverManager.registerDriver(new Driver());
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic", "root", "");

			String sql = "delete from jo_class where id=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rowCount = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rowCount;
	}

	public List<Join> getAll() {
		
		List<Join> JOs = new ArrayList<Join>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			DriverManager.registerDriver(new Driver());
			conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic","root","");
			
			String sql = "select * from jo_class";
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			while (rs.next()) {
				
				int id = rs.getInt("id");
				String jo_id = rs.getString("jo_id");
				String user_id = rs.getString("user_id");
				String jo_Sdate = rs.getString("jo_Sdate");
				String jo_Edate = rs.getString("jo_Edate");
				String cr_id = rs.getString("cr_id");
				String lo_id = rs.getString("lo_id");
				String ls_id = rs.getString("ls_id");
				String cs_id = rs.getString("cs_id");
				String op_Sdate = rs.getString("op_Sdate");
				String op_Edate = rs.getString("op_Edate");
				Join JO = new Join(id, jo_id, user_id, jo_Sdate, jo_Edate, cr_id, lo_id, ls_id, cs_id, op_Sdate, op_Edate);
				JOs.add(JO);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return JOs;
	}

	public Join getJoinById(String id) {
		Join JO = null;
		
		//int rowCount = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			DriverManager.registerDriver(new Driver());
			conn = DriverManager.getConnection("jdbc:mysql://localhost/thematic","root","");
			
			String sql = "select * from jo_class where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				
				JO = new Join(
						rs.getInt("id"), 
						rs.getString("jo_id"), 
						rs.getString("user_id"), 
						rs.getString("jo_Sdate"), 
						rs.getString("jo_Edate"),
						rs.getString("cr_id"), 
						rs.getString("lo_id"), 
						rs.getString("ls_id"), 
						rs.getString("cs_id"), 
						rs.getString("op_Sdate"), 
						rs.getString("op_Edate"));
						
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return JO;
	}
}
